#-*-coding:utf-8-*-
import datetime
from scrapy import log
from twisted.enterprise import adbapi

class SingleMySqlPipeline(object):
    def __init__(self):
        self.dbpool = adbapi.ConnectionPool('MySQLdb',
            host = self.MYSQL_SERVER,
            db = self.MYSQL_DB,
            user = self.MYSQL_USER,
            passwd =  self.MYSQL_PASS,
            charset = 'utf8',
            use_unicode = True
        )
    @classmethod
    def from_crawler(cls, crawler):
        cls.MYSQL_SERVER = crawler.settings.get('MYSQL_SERVER', 'localhost')
        cls.MYSQL_PORT = crawler.settings.getint('MYSQL_PORT', 3306)
        cls.MYSQL_DB = crawler.settings.get('MYSQL_DB', 'gather')
        cls.MYSQL_USER= crawler.settings.get('MYSQL_USER', 'root')
        cls.MYSQL_PASS = crawler.settings.get('MYSQL_PASS', 'root')
        cls.MYSQL_TB = crawler.settings.get('MYSQL_TB', 'goods')
        cls.MYSQL_TB_ATTR = crawler.settings.get('MYSQL_TB_ATTR', 'goods_attrs')
        pipe = cls()
        pipe.crawler = crawler
        #print 'init mongodb setting'
        return pipe

    def process_item(self, item, spider):
        goods = {
            'url':item.get('url'),
            'name':item.get('name'),
            'attrs':item.get('attrs'),
            'desc':item.get('desc'),
            'price':item.get('price'),
            'sort':item.get('sort'),
            'brand':item.get('brand'),
            'rank':item.get('rank'),
            'intro':item.get('intro'),
            'date':datetime.datetime.now()
        }
        log.msg('采集'+goods.get('name'))
        query = self.dbpool.runInteraction(self._conditional_insert, goods)
        query.addErrback(self.handle_error)
        return item

    def _conditional_insert(self, tx, goods):
        if goods.get('name') and goods.get('attrs'):
            tx.execute(\
                    "insert into " + self.MYSQL_TB + " (name,category,url,brand)\
                     values (%s,%s,%s,%s)",
                    (goods['name'],goods['sort'],goods['url'],goods['brand'])
                )
            id=tx.connection.insert_id()
            attrs = goods['attrs']
            #log.msg('attrs:'+str((attrs)))
            for (k,v) in  attrs.items():
                #log.msg( k+':'+v)
                tx.execute(\
                    "insert into  " + self.MYSQL_TB_ATTR + "  (`gid`,`key`,`value`)\
                     values (%s,%s,%s)",
                    (id,k,v)
                )
            #log.msg('ID:'+str(id))
    def handle_error(self, e):
        log.err(e)
